// .........................................................................
// Title: moodys_drd.do
//
// Imports data from the Moody's Debt Default & Recovery (DRD) database,
// including universe of Moody's credit ratings 
// .........................................................................

* -------------------------------------------
* Import Moody's Default and Debt Recovery
* -------------------------------------------

* make directory
cap mkdir $tmp/moodys

* raw csv imports
if 1 {

    * master issuer
    import delimited using $raw/moodys/csv/MAST_ISSR.csv, clear
    save "$raw/moodys/stata/master_issuer", replace

    * master debt
    import delimited using $raw/moodys/csv/MAST_DEBT.csv, clear
    save "$raw/moodys/stata/master_debt", replace

    * debt ids
    import delimited using $raw/moodys/csv/DEBT_IDS.csv, clear
    save "$raw/moodys/stata/debt_ids", replace

    * issuer ids
    import delimited using $raw/moodys/csv/ISSR_IDS.csv, clear
    save "$raw/moodys/stata/issuer_ids", replace

    * debt ratings
    import delimited using $raw/moodys/csv/DEBT_RATG.csv, clear
    save "$raw/moodys/stata/debt_ratings", replace

    * default history
    import delimited using $raw/moodys/csv/DFLT_HIST.csv, clear varnames(1)
    cap drop v7-v36
    save "$raw/moodys/stata/default_history", replace

    * default recovery
    import delimited using $raw/moodys/csv/DFLT_RCVRY_DEBT.csv, clear varnames(1)
    save "$raw/moodys/stata/default_recovery_debt", replace

    * master default
    import delimited using $raw/moodys/csv/MAST_DFLT.csv, clear varnames(1)
    save "$raw/moodys/stata/master_default", replace
    
}

* mdn to moodys issuer number
use "$raw/moodys/stata/master_debt", clear
keep mast_debt_num mast_issr_num
mmerge mast_issr_num using "$raw/moodys/stata/master_issuer", unmatched(m) ukeep(issuer_nam naic)
drop _merge
save "$tmp/moodys/mdn_to_moodys_issuer", replace

* cusip mapping
use "$raw/moodys/stata/debt_ids", clear
keep if id_num_cd == "CUS"
rename id_num cusip
assert ~missing(cusip)
assert ~missing(mast_debt_num)
drop id_num_cd
save "$tmp/moodys/mdn_to_cusip", replace

* cusip to moodys issuer
use "$tmp/moodys/mdn_to_moodys_issuer", clear
mmerge mast_debt_num using "$tmp/moodys/mdn_to_cusip", unmatched(m)
keep if _merge == 3
assert ~missing(cusip)
drop _merge
gsort cusip
by cusip: keep if _n == 1
order cusip
save "$tmp/moodys/cusip_to_moodys_issuer", replace

* isin mapping
use "$raw/moodys/stata/debt_ids", clear
keep if id_num_cd == "ISI"
rename id_num isin
assert ~missing(isin)
assert ~missing(mast_debt_num)
drop id_num_cd
save "$tmp/moodys/mdn_to_isin", replace

* process master debt
use "$raw/moodys/stata/master_debt", clear
mmerge mast_debt_num using "$tmp/moodys/mdn_to_cusip"
assert _merge != 2
mmerge mast_debt_num using "$tmp/moodys/mdn_to_isin"
assert _merge != 2
drop _merge
order mast_debt_num mast_issr_num cusip isin
save "$tmp/moodys/master_debt_with_ids", replace

* enhance cusips
use "$tmp/moodys/master_debt_with_ids", clear
mmerge isin using "$raw/cmns/gcap_security_master_isin", ukeep(cusip) uname(u_) unmatched(m)
replace cusip = u_cusip if missing(cusip)
drop u_cusip _merge
save "$tmp/moodys/master_debt_with_ids_xt", replace

* process ratings
use "$raw/moodys/stata/debt_ratings", clear
split ratg_datetime, parse(" ")
gen ratg_date = date(ratg_datetime1, "MDY", 2021)
format %td ratg_date
drop ratg_datetime1 ratg_datetime2 ratg_datetime

split ratg_end_datetime, parse(" ")
gen ratg_end = date(ratg_end_datetime1, "MDY", 2021)
format %td ratg_end
drop ratg_end_datetime*

drop cury_capd_txt ratg_reas_cd
drop debt_rtg_sf_ind_txt
drop ratg_typ_cd
drop ratg_clss_num

order mast_debt_num ratg_date
gsort mast_debt_num ratg_date
drop ratg_end

by mast_debt_num ratg_date: gen N = _N
by mast_debt_num ratg_date: keep if _n == 1
drop N

gen date_q = qofd(ratg_date)
format %tq date_q

gsort mast_debt_num date_q -ratg_date
by mast_debt_num date_q: keep if _n == 1
drop ratg_date
save "$tmp/moodys/mdb_ratings_compact_q", replace

* issuance and maturity dates
use "$tmp/moodys/master_debt_with_ids_xt", clear
keep mast_debt_num matr_datetime first_lt_ratg sale_datetime first_lt_ratg_datetime
replace sale_datetime = first_lt_ratg_datetime if missing(sale_datetime)
drop if missing(first_lt_ratg) & missing(matr_datetime) & missing(sale_datetime)
drop first_lt_ratg_datetime
split matr_datetime, parse(" ")
split sale_datetime, parse(" ")
gen issuance_date = date(sale_datetime1, "MDY", 2021)
gen maturity_date = date(matr_datetime1, "MDY", 2021)
gen issuance_q = qofd(issuance_date)
gen maturity_q = qofd(maturity_date)
format %td issuance_date maturity_date
format %tq issuance_q maturity_q
drop matr* sale*
save "$tmp/moodys/issuance_and_maturity_dates", replace

* ratings at issuance
use "$tmp/moodys/issuance_and_maturity_dates", clear
keep mast_debt_num issuance_q first_lt_ratg
rename first_lt_ratg ratg_txt
rename issuance_q date_q
gen _addon = 1
drop if missing(ratg_txt)
save "$tmp/issuance_rating_addon", replace

* appending
use "$tmp/moodys/mdb_ratings_compact_q", clear
append using "$tmp/issuance_rating_addon"
replace _addon = 0 if missing(_addon)
gsort mast_debt_num date_q
by mast_debt_num date_q: gen N = _N
drop if N == 2 & _addon == 1
drop N
by mast_debt_num date_q: gen N = _N
assert N == 1
drop _addon N
drop if date_q < tq(1990q1)

* balance panel
count
fillin mast_debt_num date_q
count

* merge in issuance and maturity dates
mmerge mast_debt_num using "$tmp/moodys/issuance_and_maturity_dates", unmatched(m) ukeep(issuance_q maturity_q)
drop _merge

* drops
drop if ~missing(date_q) & ~missing(issuance_q) & date_q < issuance_q
drop if ~missing(date_q) & ~missing(maturity_q) & date_q > maturity_q
save "$tmp/mdb_ratings_long_q_pre", replace

* establish cusip mapping
use "$tmp/moodys/master_debt_with_ids_xt", clear
keep if ~missing(cusip)
keep mast_debt_num cusip
save "$tmp/moodys/mdn_to_cusip_xt", replace

* carrying forward
use "$tmp/mdb_ratings_long_q_pre", clear
assert ~missing(ratg_txt) if _fillin == 0
gsort mast_debt_num date_q

by mast_debt_num: carryforward ratg_txt, gen(_ratg_txt)
replace ratg_txt = _ratg_txt if missing(ratg_txt)
drop _*

gen ratg_preferred = ratg_txt
replace ratg_preferred = "" if ratg_txt == "WR"
assert missing(ratg_preferred) if ratg_txt == "WR"
by mast_debt_num: carryforward ratg_preferred, gen(_ratg_preferred)
replace ratg_preferred = _ratg_preferred if missing(ratg_preferred)
drop _*

replace ratg_preferred = "" if strpos(ratg_txt, "VMIG") | strpos(ratg_txt, "LGD")
by mast_debt_num: carryforward ratg_preferred, gen(_ratg_preferred)
replace ratg_preferred = _ratg_preferred if missing(ratg_preferred) & _ratg_preferred != "WR"
drop _*

replace ratg_txt = ratg_preferred
drop ratg_preferred
drop if missing(ratg_txt) & date_q < tq(2000q1)

gsort mast_debt_num -date_q
by mast_debt_num: carryforward ratg_txt, gen(_ratg_txt)
replace ratg_txt = _ratg_txt if missing(ratg_txt)
drop _*
gsort mast_debt_num date_q
drop if missing(ratg_txt)

drop issuance_q maturity_q
order mast_debt_num date_q
save "$tmp/moodys/mdb_ratings_long_q", replace

* convert to cusip
use "$tmp/moodys/mdb_ratings_long_q", clear
mmerge mast_debt_num using "$tmp/moodys/mdn_to_cusip", unmatched(m)
drop if missing(cusip)
drop _merge
gen preference = 1 if ~inlist(ratg_txt, "WR")
replace preference = 2 if missing(preference)

gsort cusip date_q preference
by cusip date_q: keep if _n == 1
drop preference mast_debt_num
order cusip date_q
save "$tmp/moodys/cusip_ratings_long_q", replace

* cleaner version
use "$tmp/moodys/cusip_ratings_long_q", clear
replace ratg_txt = subinstr(ratg_txt, "(P)", "", .)
replace ratg_txt = "LGD1" if strpos(ratg_txt, "LGD1")
replace ratg_txt = "LGD2" if strpos(ratg_txt, "LGD2")
replace ratg_txt = "LGD3" if strpos(ratg_txt, "LGD3")
replace ratg_txt = "LGD4" if strpos(ratg_txt, "LGD4")
replace ratg_txt = "LGD5" if strpos(ratg_txt, "LGD5")
replace ratg_txt = "LGD6" if strpos(ratg_txt, "LGD6")
replace ratg_txt = subinstr(ratg_txt, `"""', "", .)

replace ratg_txt = "A1" if ratg_txt == "a1"
replace ratg_txt = "A2" if ratg_txt == "a2"
replace ratg_txt = "A3" if ratg_txt == "a3"

replace ratg_txt = "Aa1" if ratg_txt == "aa1"
replace ratg_txt = "Aa2" if ratg_txt == "aa2"
replace ratg_txt = "Aa3" if ratg_txt == "aa3"
replace ratg_txt = "Aaa" if ratg_txt == "aaa"

replace ratg_txt = "B1" if ratg_txt == "b1"
replace ratg_txt = "B2" if ratg_txt == "b2"
replace ratg_txt = "B3" if ratg_txt == "b3"

replace ratg_txt = "Ba1" if ratg_txt == "ba1"
replace ratg_txt = "Ba2" if ratg_txt == "ba2"
replace ratg_txt = "Ba3" if ratg_txt == "ba3"

replace ratg_txt = "Baa1" if ratg_txt == "baa1"
replace ratg_txt = "Baa2" if ratg_txt == "baa2"
replace ratg_txt = "Baa3" if ratg_txt == "baa3"

replace ratg_txt = "C" if ratg_txt == "c"
replace ratg_txt = "Ca" if ratg_txt == "ca"
replace ratg_txt = "Caa" if ratg_txt == "caa"

replace ratg_txt = subinstr(ratg_txt, "-mf", "", .)
replace ratg_txt = subinstr(ratg_txt, "-bf", "", .)
replace ratg_txt = subinstr(ratg_txt, ".", "", .)
replace ratg_txt = subinstr(ratg_txt, " ", "", .)

save "$tmp/moodys/cusip_ratings_long_simplified_q", replace

* yearly version
use "$tmp/moodys/cusip_ratings_long_simplified_q", clear
gen year = year(dofq(date_q))
gsort cusip year -date_q
by cusip year: keep if _n == 1
order cusip year
drop date_q
rename ratg_txt moodys_rating
save "$tmp/moodys/cusip_ratings_long_simplified_y", replace
